Re: [SQL] set type and in clause - Mailing list pgsql-sql

At 0:25 +0200 on 20/1/99, frank wrote:


>
> i suppose it´s been asked before (i´ve seen the question, but not the
> answer, in the mailing list archives):
>
> how do you create a set type, i.e. an attribute which is, say ´set of
> char´; and then you´d want to run some query like
>
> select  . . . from  . . . where xxx in yyy
>
> is such a data type supported by postgresql? do other dbms´s support it?

According to the relational model, a set is a separate table which is
connected to your main table by a foreign key. That is, you hold two tables:

Main table:

P.Key  1:1 Data
-----  --------
10     Microsoft
20     Adobe
30     FSF
...

Secondary table:

F.Key  1:N Data
-----  --------
10     Office
10     Access
20     Photoshop
30     gcc
10     Windows
20     Acrobat
30     bison
30     flex

In this manner, you have the strings 'Office', 'Access' and 'Windows'
associated with the record for 'Microsoft', the strings 'Photoshop' and
'Acrobat' associated with the record for 'Adobe', and the strings 'gcc',
'bison' and 'flex' associated with the record for 'FSF'.

Suppose the names of the fields are 'p' for the primary key, 'company' form
the 1:1 data, 'f' for the foreign key and 'product' for the 1:N data, you
can write a query like this:

SELECT company, product
FROM main, secondary
WHERE p=f.

The result would be:

Microsoft   Office
Microsoft   Access
Microsoft   Windows
Adobe       Photoshop
Adobe       Acrobat
FSF         gcc
FSF         bison
FSF         flex

The point I am trying to get across is that this, in fact, represents a set
associated with each record. You get the data you wanted. The problem is
that in the result you get repetitions, because that's how the relational
model works. If you want to isolate the set, you can use a frontend which
drops the repetitions and creates the following report from them:

Microsoft   Office
            Access
            Windows
Adobe       Photoshop
            Acrobat
FSF         gcc
            bison
            flex

Nevertheless, the data represented by this report is the same data that you
got from the original query. It merely shows association with the company.
So, if you wanted to test whether something belongs or does not belong to a
set that is associated with one of your software vendors, you can do a
subquery on the table. For example, to get the companies which have in
their set of products a product called 'Acrobat', you write:

SELECT company
FROM main
WHERE 'Acrobat' IN (
  SELECT product
  FROM secondary
  WHERE p=f );

I'd like to note that having the set of related products included in the
main table is, well, not relational.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma